import pandas as pd
import numpy as np

def process_excel_files(pos_file, neg_file, output_file):
    # Read the Excel files
    df_pos = pd.read_excel(pos_file, index_col=None)
    df_neg = pd.read_excel(neg_file, index_col=None)
    
    # Reset index
    df_pos = df_pos.reset_index(drop=True)
    df_neg = df_neg.reset_index(drop=True)
    
    df_pos.insert(0, 'Index', [f'Pos_{i+1}' for i in range(len(df_pos))])
    df_neg.insert(0, 'Index', [f'Neg_{i+1}' for i in range(len(df_neg))])
    
    # Round MW 
    df_pos['MW_rounded'] = df_pos['Calc. MW'].round(2)
    df_neg['MW_rounded'] = df_neg['Calc. MW'].round(2)
    
    # Create result lists
    paired_rows = []
    unpaired_pos_indices = set(df_pos.index)
    unpaired_neg_indices = set(df_neg.index)
    
    # Pair tracking
    pair_counter = 1
    
    # Iterate through positive file rows
    for idx_pos in df_pos.index:
        row_pos = df_pos.loc[idx_pos]
        
        # Find potential matches in negative file
        matches = df_neg[
            (abs(df_neg['RT [min]'] - row_pos['RT [min]']) < 0.5) & 
            (df_neg['MW_rounded'] == row_pos['MW_rounded']) &
            (df_neg['Formula'] == row_pos['Formula'])
        ]
        
        # If matches found
        if not matches.empty:
            # Take the first match
            match_idx = matches.index[0]
            row_neg = df_neg.loc[match_idx]
            
            # Create paired rows
            paired_pos_row = row_pos.copy()
            paired_neg_row = row_neg.copy()
            
            # Add pairing tag
            paired_pos_row['POS_NEG'] = pair_counter
            paired_neg_row['POS_NEG'] = pair_counter
            
            # Add to paired rows
            paired_rows.append(paired_pos_row)
            paired_rows.append(paired_neg_row)
            
            # Remove paired indices
            if idx_pos in unpaired_pos_indices:
                unpaired_pos_indices.remove(idx_pos)
            if match_idx in unpaired_neg_indices:
                unpaired_neg_indices.remove(match_idx)
            
            # Increment pair counter
            pair_counter += 1
    
    # Process remaining unpaired rows from positive file
    for idx in unpaired_pos_indices:
        row = df_pos.loc[idx].copy()
        row['POS_NEG'] = 'unpaired'
        paired_rows.append(row)
    
    # Process remaining unpaired rows from negative file
    for idx in unpaired_neg_indices:
        row = df_neg.loc[idx].copy()
        row['POS_NEG'] = 'unpaired'
        paired_rows.append(row)
    
    # Convert to DataFrame
    result_df = pd.DataFrame(paired_rows)
   
    # Drop the MW_rounded column
    if 'MW_rounded' in result_df.columns:
        result_df = result_df.drop(columns=['MW_rounded'])
    
    # Save to Excel
    result_df.to_excel(output_file, index=False)
    
    print(f"Processing complete. Output saved to {output_file}")
    print(f"Total paired rows: {pair_counter - 1}")
    print(f"Total unpaired rows: {len(result_df[result_df['POS_NEG'] == 'unpaired'])}")


process_excel_files('Data/POS_NEG/20250331-pos.xlsx', 'Data/POS_NEG/20250331-neg.xlsx', 'Data/paired_results_20250331.xlsx')